﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Models;
using DBUtlity;

namespace DAL
{
    public class BookService
    {
        /// <summary>
        /// 获取全部的图书分类
        /// </summary>
        /// <returns></returns>
        public List<Category> GetAllCategory()
        {
            string sql = "select CategoryId,CategoryName from Categories";
            List<Category> list = new List<Category>();
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            while (objReader.Read())
            {
                list.Add(new Category()
                {
                    CategoryId = Convert.ToInt32(objReader["CategoryId"]),
                    CategoryName = objReader["CategoryName"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        /// <summary>
        /// 获取全部的出版社
        /// </summary>
        /// <returns></returns>
        public List<Publisher> GetAllPublisher()
        {
            string sql = "select PublisherId,PublisherName from Publishers";
            List<Publisher> list = new List<Publisher>();
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            while (objReader.Read())
            {
                list.Add(new Publisher()
                {
                    PublisherId = Convert.ToInt32(objReader["PublisherId"]),
                    PublisherName = objReader["PublisherName"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        /// <summary>
        /// 判断条码是否存在
        /// </summary>
        /// <param name="barCode"></param>
        /// <returns></returns>
        public int GetCountByCarCode(string barCode)
        {
            string sql = "select count(*) from Books where BarCode = @BarCode";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@BarCode",barCode)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
        /// <summary>
        /// 新增图书
        /// </summary>
        /// <param name="objBook"></param>
        /// <returns></returns>
        public int AddBook(Book objBook)
        {
            //封装参数
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@BarCode",objBook.BarCode),
                new SqlParameter("@BookName",objBook.BookName),
                new SqlParameter("@Author",objBook.Author),
                new SqlParameter("@PublisherId",objBook.PublisherId),
                new SqlParameter("@PublishDate",objBook.PublishDate),
                new SqlParameter("@BookCategory",objBook.BookCategory),
                new SqlParameter("@UnitPrice",objBook.UnitPrice),
                new SqlParameter("@BookImage",objBook.BookImage),
                new SqlParameter("@BookCount",objBook.BookCount),
                new SqlParameter("@Remainder",objBook.Remainder),
                new SqlParameter("@BookPosition",objBook.BookPosition)
            };
            return SQLHelper.UpdateByProcedure("usp_AddBook", param);

        }

        #region 图书上架

        public Book GetBookByBarCode(string barCode)
        {
            string sql = "select BookId,BarCode,BookName,Author,Books.PublisherId,PublishDate,";
            sql += "BookCategory,UnitPrice,BookImage,BookCount,Remainder,BookPosition,RegTime,";
            sql += "PublisherName,CategoryName from Books ";
            sql += "inner join Publishers on Books.PublisherId=Publishers.PublisherId ";
            sql += "inner join Categories on Books.BookCategory=Categories.CategoryId ";
            sql += "where Barcode= @BarCode";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@BarCode",barCode)
            };
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            Book objBook = null;
            if (objReader.Read())
            {
                objBook = new Book()
                {
                    BoolId = Convert.ToInt32(objReader["BookId"]),
                    BarCode = objReader["BarCode"].ToString(),
                    BookName = objReader["BookName"].ToString(),
                    Author = objReader["Author"].ToString(),
                    PublisherId = Convert.ToInt32(objReader["PublisherId"]),
                    PublishDate = Convert.ToDateTime(objReader["PublishDate"]),
                    BookCategory = Convert.ToInt32(objReader["BookCategory"]),
                    UnitPrice = Convert.ToDouble(objReader["UnitPrice"]),
                    BookImage = objReader["BookImage"] is DBNull ? "" : objReader["BookImage"].ToString(),
                    BookCount = Convert.ToInt32(objReader["BookCount"]),
                    Remainder = Convert.ToInt32(objReader["Remainder"]),
                    BookPosition = objReader["BookPosition"].ToString(),
                    RegTime = Convert.ToDateTime(objReader["RegTime"]),
                    PublisherName = objReader["PublisherName"].ToString(),
                    CategoryName = objReader["CategoryName"].ToString()
                };
            }
            objReader.Close();
            return objBook;
        }
        /// <summary>
        /// 更新图书收藏总数
        /// </summary>
        /// <param name="barCode"></param>
        /// <param name="bookCount"></param>
        /// <returns></returns>
        public int AddBookCount(string barCode, int bookCount)
        {
            string sql = "update Books set BookCount=BookCount+@BookCount ";
            sql += "where BarCode=@BarCode";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@BarCode",barCode),
                new SqlParameter("@BookCount",bookCount)
            };
            return SQLHelper.Update(sql, param);
        }
        #endregion

        #region 图书维护
        /// <summary>
        /// 根据组合条件查询图书信息
        /// </summary>
        /// <param name="categoryId"></param>
        /// <param name="barCode"></param>
        /// <param name="bookName"></param>
        /// <returns></returns>

        public List<Book> GetBooks(int categoryId,string barCode,string bookName)
        {
            List<SqlParameter> paramList = new List<SqlParameter>();
            string sql = "select BookId,BarCode,BookName,Author,Books.PublisherId,PublishDate,";
            sql += "BookCategory,UnitPrice,BookImage,BookCount,Remainder,BookPosition,RegTime,";
            sql += "PublisherName,CategoryName from Books ";
            sql += "inner join Publishers on Books.PublisherId=Publishers.PublisherId ";
            sql += "inner join Categories on Books.BookCategory=Categories.CategoryId ";
            sql += "where 1=1 ";
            if(barCode!=null&& barCode.Length>0)
            {
                sql += "and BarCode=@BarCode";
                paramList.Add(new SqlParameter("@BarCode", barCode));
            }
            else
            {
                if(categoryId!=-1)
                {
                    sql += "and CategoryId=@CategoryId";
                    paramList.Add(new SqlParameter("@CategoryId", categoryId));
                }
                if(bookName!=null&&bookName.Length>0)
                {
                    sql += "and BookName like @BookName+'%'";
                    paramList.Add(new SqlParameter("@BookName", bookName));
                }
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, paramList.ToArray());
            List<Book> bookList = new List<Book>();
            while(objReader.Read())
            {
                bookList.Add(new Book()
                {
                    BoolId = Convert.ToInt32(objReader["BookId"]),
                    BarCode = objReader["BarCode"].ToString(),
                    BookName = objReader["BookName"].ToString(),
                    Author = objReader["Author"].ToString(),
                    PublisherId = Convert.ToInt32(objReader["PublisherId"]),
                    PublishDate = Convert.ToDateTime(objReader["PublishDate"]),
                    BookCategory = Convert.ToInt32(objReader["BookCategory"]),
                    UnitPrice = Convert.ToDouble(objReader["UnitPrice"]),
                    BookImage = objReader["BookImage"] is DBNull ? "" : objReader["BookImage"].ToString(),
                    BookCount = Convert.ToInt32(objReader["BookCount"]),
                    Remainder = Convert.ToInt32(objReader["Remainder"]),
                    BookPosition = objReader["BookPosition"].ToString(),
                    RegTime = Convert.ToDateTime(objReader["RegTime"]),
                    PublisherName = objReader["PublisherName"].ToString(),
                    CategoryName = objReader["CategoryName"].ToString()
                });
            }
            objReader.Close();
            return bookList;
        }
        /// <summary>
        /// 修改图书对象
        /// </summary>
        /// <param name="objBook"></param>
        /// <returns></returns>
        public int EditBook(Book objBook)
        {
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@BookId",objBook.BoolId),
                new SqlParameter("@BookName",objBook.BookName),
                new SqlParameter("@Author",objBook.Author),
                new SqlParameter("@PublisherId",objBook.PublisherId),
                new SqlParameter("@PublishDate",objBook.PublishDate),
                new SqlParameter("@BookCategory",objBook.BookCategory),
                new SqlParameter("@UnitPrice",objBook.UnitPrice),
                new SqlParameter("@BookImage",objBook.BookImage),
                new SqlParameter("@BookCount",objBook.BookCount),
                new SqlParameter("@BookPosition",objBook.BookPosition)
            };
            return SQLHelper.UpdateByProcedure("usp_EditBook", param);
        }
        #endregion
    }
}
